package com.tangw.manage.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.tangw.manage.dao.UserDao;
import com.tangw.manage.model.User;
import com.tangw.manage.util.DBUtil;
import com.tangw.manage.util.MD5;

/**
 * 
 * @author TangW
 * 
 */
public class UserDaoImpl implements UserDao {
	private Connection connection = DBUtil.getConnection();

	@Override
	public User getUserByUsernameAndPassword(String username, String password) {
		String sql = " SELECT * FROM T_USERS WHERE USERNAME = ? AND PASSWORD = ? ";
		try (PreparedStatement prep = connection.prepareStatement(sql)) {
			prep.setString(1, username);
			prep.setString(2, MD5.getMD5(password));
			if (prep.execute()) {
				ResultSet rs = prep.getResultSet();
				while (rs.next()) {
					User user = new User();
					user.setId(rs.getInt("ID"));
					user.setName(rs.getString("NAME"));
					user.setSex(rs.getInt("SEX"));
					user.setAge(rs.getInt("AGE"));
					user.setUsername(rs.getString("USERNAME"));
					user.setAddress(rs.getString("ADDRESS"));
					user.setCreate_time(rs.getDate("CREATE_TIME"));
					return user;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public String getPasswordByUsername(String username) throws Exception {
		String sql = " SELECT PASSWORD FROM T_USERS WHERE USERNAME = ? ";
		try (PreparedStatement prep = connection.prepareStatement(sql)) {
			prep.setString(1, username);
			if (prep.execute()) {
				ResultSet rs = prep.getResultSet();
				while (rs.next()) {
					return rs.getString("PASSWORD");
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public boolean insertUser(User user) throws Exception {
		String sql = " INSERT INTO T_USERS (ID,NAME,SEX,AGE,USERNAME,PASSWORD,ADDRESS) "
				+ " VALUES (T_USERS_SEQ.nextVal,?,?,?,?,?,?) ";
		try (PreparedStatement prep = connection.prepareStatement(sql)) {
			prep.setString(1, user.getName());
			prep.setInt(2, user.getSex());
			prep.setInt(3, user.getAge());
			prep.setString(4, user.getUsername());
			prep.setString(5, MD5.getMD5(user.getPassword()));
			prep.setString(6, user.getAddress());
			prep.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public Connection getConnection() {
		return connection;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
}
